package cn.pconline.autoclub.service;

import cn.pconline.autoclub.config.SystemConfig;
import cn.pconline.autoclub.entity.Activity;
import cn.pconline.autoclub.entity.Club;
import cn.pconline.autoclub.entity.ClubIntegral;
import cn.pconline.autoclub.entity.ClubMember;
import cn.pconline.autoclub.entity.ClubTotalMember;
import cn.pconline.autoclub.entity.DynaImage;
import cn.pconline.autoclub.entity.MemberApplication;
import cn.pconline.autoclub.entity.MemberStat;
import cn.pconline.autoclub.entity.OperateLog;
import cn.pconline.autoclub.entity.Topic;
import cn.pconline.autoclub.entity.User;
import cn.pconline.autoclub.util.DateUtils;
import cn.pconline.autoclub.util.Pager;
import cn.pconline.autoclub.util.StringUtil;
import cn.pconline.autoclub.util.WebUtils;
import cn.pconline.autoclub.util.dyna.AbstractDyna;
import cn.pconline.autoclub.util.dyna.PostReply;
import cn.pconline.autoclub.util.dyna.TopicDyna;
import cn.pconline.r.client.SimpleHttpTemplate;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.gelivable.dao.GeliDao;
import org.gelivable.dao.GeliOrm;
import org.gelivable.dao.SqlBuilder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 *
 * @author wanganning
 */
public class MemberService {

    @Autowired
    private GeliDao geliDao;
    @Autowired
    private GeliOrm geliOrm;
    @Autowired
    private UserService userService;
    @Autowired
    private ClubService clubService;
    @Autowired
    private MessageService messageService;
    @Autowired
    private SimpleHttpTemplate simpleHttpTemplate;
    @Autowired
    private PetrolService petrolService;
    @Autowired
    private SystemConfig systemConfig;
    @Autowired
    private TopicService topicService;
    @Autowired
    private CommentService commentService;
    @Autowired
    private AlbumService albumService;
    @Autowired
    private StatService statService;
    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    private PagerService pagerService;
    @Autowired
    private CheckInService checkInService;
    @Autowired
    private ActivityService activityService;
    @Autowired
    private IntfService intfService;
    @Autowired
    private DynaService dynaService;
    private static final Logger log = LoggerFactory.getLogger(MemberService.class);

    public ClubMember findMember(long memberId) {
        try {
            return geliDao.find(ClubMember.class, memberId);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * 根据用户id和车友会id查找车友会成员
     *
     * @param uid 用户id
     * @param clubId 车友会id
     * @return 成员
     */
    public ClubMember findMember(long uid, long clubId) {
        if (uid == 0 || clubId == 0) {
            return null;
        }

        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("SELECT id FROM ac_member_info WHERE 1= 1");
        sql.appendSql(" and user_id = ").appendValue(uid);
        sql.appendSql(" and club_id = ").appendValue(clubId);
        List<ClubMember> list = geliDao.list(ClubMember.class, sql.getSql(), sql.getValues());

        return list.isEmpty() ? null : list.get(0);
    }

    /**
     * 判断某用户是否是某车友会会员
     *
     * @param uid 用户id
     * @param clubId 车友会id
     * @return
     */
    public boolean isClubMember(long uid, long clubId) {
        if (uid == 0 || clubId == 0) {
            return false;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("SELECT count(1) FROM ac_member_info WHERE 1= 1");
        sql.appendSql(" and user_id = ").appendValue(uid);
        sql.appendSql(" and club_id = ").appendValue(clubId);
        return geliDao.count(sql.getSql(), sql.getValues()) > 0;
    }

    /**
     * 判断用户是否是管理员
     *
     * @param uid
     * @param clubId
     * @return
     */
    public boolean isClubNormalAdmin(long uid, long clubId) {
        ClubMember clubMember = findMember(uid, clubId);
        if (clubMember == null) {
            return false;
        }
        return (clubMember.isAdmin() || clubMember.isViceAdmin());
    }

    /**
     * 判断用户是否是会长
     *
     * @param uid
     * @param clubId
     * @return
     */
    public boolean isClubOwner(long uid, long clubId) {
        ClubMember clubMember = findMember(uid, clubId);
        if (clubMember == null) {
            return false;
        }
        return (clubMember.isAdmin());
    }

    /**
     * 根据主键查找车友会成员
     *
     * @param id
     * @return
     */
    public ClubMember find(long id) {
        try {
            return geliDao.find(ClubMember.class, id);
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 根据主键查找车友会加入申请
     *
     * @param id 申请id
     * @return
     */
    public MemberApplication findMemberApplication(long id) {
        try {
            return geliDao.find(MemberApplication.class, id);
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 根据用户和车友会查找加入申请
     *
     * @param uid 用户id
     * @param clubId 车友会id
     * @param unaudited 是否只查未审的
     * @return 申请
     */
    public List<MemberApplication> findMemberApplication(long uid, long clubId, boolean unaudited) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("SELECT * FROM ac_member_application WHERE 1=1 ");
        if (uid > 0) {
            sql.appendSql(" AND user_id= ").appendValue(uid);
        }
        if (clubId > 0) {
            sql.appendSql(" AND club_id= ").appendValue(clubId);
        }
        if (unaudited) {
            sql.appendSql(" AND status=0 ");
        }
        sql.appendSql(" order by create_at ");
        return geliDao.list(MemberApplication.class, sql.getSql(), sql.getValues());
    }

    /**
     * 创建入会申请
     *
     * @param user 用户
     * @param club 车友会
     * @return
     */
    public int applyMembership(User user, Club club) {
        if (user == null || club == null) {
            return 0;
        }
        MemberApplication app = new MemberApplication();
        app.setUid(user.getUid());
        app.setClubId(club.getClubId());
        long id = geliDao.create(app);
        return id > 0 ? 1 : 0;
    }

    /**
     * 列出待审的入会申请
     *
     * @param clubId 车友会id
     * @return
     */
    public List<MemberApplication> listMemberApplications4Audit(long clubId) {
        String sql = "SELECT * FROM ac_member_application WHERE club_id=? AND status=0";
        List<MemberApplication> list = geliDao.list(MemberApplication.class, sql, clubId);
        return userService.loadTransientUsers(list);
    }

    /**
     * 查询用户待审的入会申请
     *
     * @param userId 用户id
     * @return
     */
    public List<MemberApplication> listMemberApplicationsByUserId(long userId) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("SELECT * FROM ac_member_application WHERE user_id=").appendValue(userId).appendSql(" and status=0");
        List<MemberApplication> list = geliDao.list(MemberApplication.class, sql.getSql(), sql.getValues());
        return list;
    }

    /**
     * 分页列出车友会成员信息
     *
     * @param clubId 查某车友会时,车友会的id
     * @param uid 查某用户加入的多个车友会时,用户id
     * @param pageNo 分页号
     * @param pageSize 每页显示
     * @return
     */
    public Pager<ClubMember> getClubMemberPage(long clubId, long uid, int pageNo, int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("SELECT id FROM ac_member_info WHERE 1=1 ");
        //先user后club能用到索引
        if (uid > 0) {
            sql.appendSql(" AND user_id= ").appendValue(uid);
        }

        if (clubId > 0) {
            sql.appendSql(" AND club_id= ").appendValue(clubId);
        }

        sql.appendSql(" ORDER BY admin_type DESC, join_at ASC");
        Pager<ClubMember> pager = pagerService.pager(ClubMember.class, sql, pageNo, pageSize);
        if (pager.getResultList() != null && (!pager.getResultList().isEmpty())) {
            pager.setResultList(userService.loadTransientUsers(pager.getResultList()));
        }
        return pager;
    }

    /**
     * 加入的车友会列表
     *
     * @param uid
     * @return
     */
    public List<ClubMember> listClubMemberByUser(long uid) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("SELECT id FROM ac_member_info WHERE user_id=").appendValue(uid);//只有加入一个车友会了，不用排序.appendSql(" order by join_at ");
        return geliDao.list(ClubMember.class, sql.getSql(), sql.getValues());
    }

    /**
     * 根据userID查找加入的车友会列表
     *
     * @param userId
     * @return
     */
    public ClubMember findClubMemberByUserId(long userId) {
        SqlBuilder sqlBuilder = new SqlBuilder();
        sqlBuilder.appendSql("SELECT * FROM ac_member_info WHERE user_id=").appendValue(userId);//只有加入一个车友会了，不用排序.appendSql(" order by join_at ");
        ClubMember clubMember = null;
        try{
            clubMember = geliDao.findFirst(ClubMember.class, sqlBuilder.getSql(), sqlBuilder.getValues());
        }catch(DataAccessException ex){
            log.error("find member error :"+userId);
        }
        return clubMember;
    }

    /**
     * 用用户名和车友会名查询,分页列出车友会成员信息 如果车友会名或用户名没有匹配到任何车友会跟用户,会视作没有限制该条件
     * (如果要视作查出无结果,需修改此方法) (车友会名称有重复时有问题)
     *
     * @param clubName
     * @param pageNo 分页号
     * @param username
     * @param pageSize 每页显示
     * @return
     */
    public Pager<ClubMember> getClubMemberPage(String clubName, String username, int pageNo, int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("SELECT a.id FROM ac_member_info a , ac_club b , ac_user c WHERE a.club_id = b.club_id AND a.user_id = c.user_id  ");
        //先user后club能用到索引
        if (StringUtils.isNotBlank(clubName)) {
            sql.appendSql(" AND b.name = ").appendValue(clubName);
        }

        if (StringUtils.isNotBlank(username)) {
            sql.appendSql(" AND c.username= ").appendValue(username);
        }

        sql.appendSql(" ORDER BY admin_type DESC, petrol DESC");
        Pager<ClubMember> pager = pagerService.pager(ClubMember.class, sql, pageNo, pageSize);
        if (pager.getResultList() != null && (!pager.getResultList().isEmpty())) {
            pager.setResultList(userService.loadTransientUsers(pager.getResultList()));
            pager.setResultList(clubService.loadTransientClubs(pager.getResultList()));
        }
        return pager;
    }

    public List<ClubMember> listNewMembers(long clubId, int count) {
        String sql = "SELECT id FROM ac_member_info WHERE club_id=? ORDER BY join_at DESC LIMIT ?";
        List<ClubMember> list = geliDao.list(ClubMember.class, sql, clubId, count);
        return userService.loadTransientUsers(list);
    }

    /**
     * 列出某车友会的管理员
     *
     * @param clubId 车友会id
     * @return
     */
    public List<ClubMember> listAdmins(long clubId) {
        String sql = "SELECT id FROM ac_member_info WHERE club_id=? AND admin_type>0 ORDER BY admin_type DESC";
        List<ClubMember> admins = geliDao.list(ClubMember.class, sql, clubId);
        return userService.loadTransientUsers(admins);
    }

    /**
     * 查询某车友会下的普通成员数
     *
     * @param clubId
     * @return
     */
    public int countMember(long clubId) {
        String sql = "select count(1) from ac_member_info where club_id = ? and admin_type=0";
        return geliDao.count(sql, clubId);
    }
    /**
     * 查询某车友会下的申请人数
     * @param clubId
     * @return 
     */
    public int countMemberApplication(long clubId){
        String sql = "SELECT count(1) FROM ac_member_application WHERE club_id=? AND status=0";
        return geliDao.count(sql, clubId);
    }
    /**
     * 列出某车友会的会员列表
     *
     * @param clubId 车友会id
     * @return
     */
    public List<ClubMember> listMemberByClubId(long clubId) {
        if (clubId < 1) {
            return null;
        }
        String sql = "SELECT id FROM ac_member_info WHERE club_id=? ";
        List<ClubMember> listMember = null;
        try {
            listMember = geliDao.list(ClubMember.class, sql, clubId);
        } catch (DataAccessException e) {

            log.error("根据clubid：" + clubId + "找不到车友会会员列表");
        }
        return userService.loadTransientUsers(listMember);
    }

    /**
     * 审核入会申请 会发系统通知
     *
     * @param application 申请
     * @param auditById 审核人id
     * @return 1:成功,0:失败
     */
    public boolean passMemberApplication(MemberApplication application, long auditById) {
        User user = userService.findUser(application.getUid());
        int count = userService.updateJoinedClubCount(user);
        if (count >= NumberUtils.toInt(systemConfig.getJoinClubLimit(), 1)) {
            return false;
        }
        application.setStatus(MemberApplication.STATUS_PASS);
        application.setAuditBy(auditById);
        application.setAuditAt(DateUtils.getNow());
        int result = geliDao.update(application);
        Club club = clubService.getClub(application.getClubId());
        String msg = messageService.assembleClubUrl(club.getClubUrl(), club.getName());
        if (result > 0) {
            long id = createMemberInfo(application.getClubId(), user, 0);
            //通过 发通知
            messageService.send(user, MessageService.APPLY_PASS, msg);
            return id > 0;
        }
        return false;
    }

    /**
     * 审核入会申请 会发系统通知
     *
     * @param application 申请
     * @param auditById 审核人id
     * @return 1:成功,0:失败
     */
    public boolean rejectMemberApplication(MemberApplication application, long auditById) {
        User user = userService.findUser(application.getUid());
        application.setStatus(MemberApplication.STATUS_REJECT);
        application.setAuditBy(auditById);
        application.setAuditAt(DateUtils.getNow());
        int result = geliDao.update(application);
        Club club = clubService.getClub(application.getClubId());
        String msg1 = messageService.assembleClubUrl(club.getClubUrl(), club.getName());
        //拒绝 发通知
        messageService.send(user, MessageService.APPLY_REFUSED, msg1);
        return result > 0;
    }

    /**
     * 创建会员信息(有不经过申请直接创建会员的情况) 创建会员信息时，会去查询该会员是否是vip
     *
     * @param user
     * @param clubId 车友会id
     * @param adminType 是否管理员,0:无,1:副会长,2:会长
     * @return
     */
    public long createMembership(User user, long clubId, int adminType) {
        ClubMember member = findMember(user.getUid(), clubId);
        if (member == null) {
            member = new ClubMember();
        }
        member.setUid(user.getUid());
        member.setClubId(clubId);
        member.setAdminType(adminType);
        member.setJoinAt(DateUtils.getNow());
        long memberId = geliDao.generateId(ClubMember.class);
        member.setId(memberId);
        //long result = geliDao.create(member);
        String sql = geliOrm.getCreateSql(member);
        String orgTbl = geliOrm.getTableName(member.getClass());
        sql = sql.replace(sql.substring(0, sql.indexOf(orgTbl)), "insert ignore into ");
        Map<String, Object> params = geliOrm.map(member);
        log.info("member sql: {}, params: {}", sql, params);
        int insertCnt = geliDao.getNamedParameterJdbcTemplate().update(sql, params);
        //geliDao.getJdbcTemplate().update(sql, args);
        if (memberId > 0 && insertCnt > 0) {
            user.setJoinedClubCount(user.getJoinedClubCount() + 1);
            userService.updateUser(user);
            //添加到排行榜
            statService.createMemberAll(user.getUid(), clubId);
            statService.createMemberWeek(user.getUid(), clubId);
            clubService.increaseMemberNum(clubId);
            String vipInfo = getVipInfo(user.getUid());
            if (StringUtils.isNotBlank(vipInfo)) {
                userService.setVip(user.getUid(), vipInfo);
                clubService.increaseVipNum(clubId);
                ClubIntegral i = petrolService.getById(PetrolService.ADD_CLUB_PETROL_ITEM_VIP);
                //增加车友会成员直接加分
                petrolService.addToClubWithinLimit(clubId, i.getIntegralId(), i.getPetrol(), i.getName(), false);
            } else if (user.getVip() == 1) {
                userService.cancelVip(user.getUid());//修正数据
            }
            ClubIntegral i = petrolService.getById(PetrolService.ADD_CLUB_PETROL_ITEM_MEMBER);
            //增加车友会成员直接加分
            petrolService.addToClubWithinLimit(clubId, i.getIntegralId(), i.getPetrol(), i.getName(), false);
        } else {
            geliDao.deleteCache(member.getClass(), member.getId());
            log.info("the user[{}] was already joined club[{}]", user.getUid(), clubId);
        }
        return memberId;
    }

    /**
     * 会长权限授予解除
     *
     * @param operator
     * @param member 要授予给的会员
     * @param adminType 1:副会长,2:会长,0:解除
     * @return 1:成功,0:失败
     */
    public int adminAssignment(ClubMember operator, ClubMember member, int adminType) {
        if (operator == null || member == null) {
            return 0;
        }
        Club club = geliDao.find(Club.class, member.getClubId());
        int oldAdminType = member.getAdminType();
        member.setAdminType(adminType);
        int result = geliDao.update(member, "admin_type");
        if (result > 0) {
            OperateLog oLog = new OperateLog();
            oLog.setUid(member.getUid());
            oLog.setOperatorId(operator.getUid());
            oLog.setCreateAt(DateUtils.getNow());
            oLog.setDetail(OperateLog.ADMIN_ASSIGNMENT + adminType);
            geliDao.create(oLog);
            //发送系统消息
            User user = userService.findUser(member.getUid());
            String passMsg = parsePassMsg(adminType, oldAdminType, member, club);
            if (StringUtils.isNotBlank(passMsg)) {
                messageService.send(user, passMsg);
            }
        }

        if (adminType == ClubMember.ADMIN) {
            //同步车友会的会长ID
            club.setAdminId(member.getUid());
            geliDao.update(club, "admin_id");
            //将被转移的会长角色设为普通角色
            return adminAssignment(operator, operator, ClubMember.NON_ADMIN);
        }
        return result;
    }

    /**
     * 开除会员
     *
     * @param operator 操作者
     * @param member 被开除者
     * @return 1:成功,0:失败
     */
    public int expelMember(ClubMember operator, ClubMember member) {
        if (operator == null || member == null) {
            return 0;
        }
        if (member.getAdminType() == ClubMember.ADMIN) {
            return 0;
        }

        long userId = member.getUid();
        long clubId = member.getClubId();
        int totalPetrol = member.getPetrol();

        /**
         * 移除前先拿到被开除者所参加的所有活动
         */
        List<Activity> activities = activityService.listActivityByUser(userId, clubId);

        int result = geliDao.delete(member, member.getId());
        //删除该用户在此车友会的申请记录
        this.deleteRemainingApplications(member.getUid(), clubId);
        if (result > 0) {
            User user = userService.findUser(userId);
            user.setJoinedClubCount(user.getJoinedClubCount() - 1);
            geliDao.update(user, "joined_club_count");
            OperateLog oLog = new OperateLog();
            oLog.setUid(userId);
            oLog.setOperatorId(operator.getUid());
            oLog.setCreateAt(DateUtils.getNow());
            oLog.setDetail(OperateLog.MEMBER_EXPEL);
            geliDao.create(oLog);
            if (user.getVip() == 1) {
                clubService.decreaseVipNum(clubId);
                ClubIntegral i = petrolService.getById(PetrolService.ADD_CLUB_PETROL_ITEM_VIP_QUIT);
                petrolService.addToClubWithinLimit(clubId, i.getIntegralId(), i.getPetrol(), i.getName(), false);
            }
            clubService.decreaseMemberNum(clubId);
            ClubIntegral i = petrolService.getById(PetrolService.ADD_CLUB_PETROL_ITEM_MEMBER_QUIT);
            petrolService.addToClubWithinLimit(clubId, i.getIntegralId(), i.getPetrol(), i.getName(), false);
            //删除帖子
            topicService.batchDeleteTopic(userId, clubId);
            //删除留言
            commentService.batchDeleteComment(userId, clubId);
            //删除相册
            albumService.batchDeleteAlbum(userId, clubId);
            //删除签到记录
            checkInService.deleteCheckIn(userId, clubId);
            //更新排行统计
            statService.deleteMemberStat(userId, clubId);
            //删除车友会周排行和全部排行
            statService.deleteMemberAll(userId, clubId);
            statService.deleteMemberWeek(userId, clubId);
            //删除活动报名记录
            activityService.deleteActityMember(member.getId());
            //车友会扣除该会员的所有总分 扣分所以要传负数
            petrolService.addToClubWithinLimit(clubId, -1, -totalPetrol, String.format("expel member(%s) from club(%s)", userId, clubId), false);
            //删除车友会动态索引
            dynaService.deleteClubDynaByUserId(clubId, userId);
            /**
             * 同步活动参加人数*
             */
            for (Activity a : activities) {
                activityService.refshActivityJoinNum(a.getActivityId(), clubId);
            }
        }
        return result;
    }

    /**
     * 获取状态为通过的车主认证信息
     *
     * @param userId
     * @return
     */
    public String getVipInfo(long userId) {
        String url = systemConfig.getBbsRoot() + "/plugin/vip/showCar.ajax?vids=" + userId;
        String relUrl = systemConfig.getRoot() + "/memberService.isVip";
        JSONArray ja = JSONArray.parseArray(simpleHttpTemplate.get(url, relUrl));
        if (ja != null && ja.size() > 0) {
            JSONObject jo = ja.getJSONObject(0);
            String serialName = jo.getString("serialname");
            String serialId = jo.getString("serialid");
            String brandName = jo.getString("brandname");
            String brandId = jo.getString("brandid");
            StringBuilder sb = new StringBuilder(32);
            int status = jo.getIntValue("status");
            if (status != 1) { //当车主认证还没通过时返回空
                return "";
            }
            sb.append(brandId).append(";")
                    .append(brandName).append(";")
                    .append(serialId).append(";")
                    .append(serialName);
            return sb.toString();
        }
        return "";
    }

    /**
     * 根据车友会ID和用户id查询该用户 TODO 用到的几个地方可以改下
     *
     * @param clubId
     * @param uid
     * @return
     */
    public ClubMember countByclubIdUid(long clubId, long uid) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select * from ac_member_info where club_id = ").appendValue(clubId);
        sql.appendSql(" and user_id = ").appendValue(uid);
        return geliDao.findFirst(ClubMember.class, sql.getSql(), sql.getValues());
    }

    private void deleteRemainingApplications(long uid, long clubId) {
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM ac_member_application WHERE user_id=? and club_id = ?");
        geliDao.getJdbcTemplate().update(sql.toString(), uid, clubId);
    }

    /**
     * 根据车友会ID，用户职务查询会员列表 0》》普通用户，1》》副会长 2》》会长 9》》管理员
     *
     * @param clubId
     * @param memberType
     * @return
     */
    public Pager<ClubMember> pagerMemberByType(long clubId, int memberType, int pageNo, int pageSize) {
        if (clubId < 1) {
            return null;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select id from ac_member_info where 1=1 ");
        sql.appendSql(" and club_id = ").appendValue(clubId);
        if (memberType == 9) {
            sql.appendSql(" and admin_type >0 ");
        } else {
            sql.appendSql(" and admin_type = ").appendValue(memberType);
        }
        Pager<ClubMember> pager = pagerService.pager(ClubMember.class, sql, pageNo, pageSize);
        if (pager.getResultList() != null && !pager.getResultList().isEmpty()) {
            pager.setResultList(userService.loadTransientUsers(pager.getResultList()));
        }
        return pager;
    }

    /**
     * 根据车友会ID查找所有会员列表
     *
     * @param clubId
     * @return
     */
    public Pager<ClubMember> pagerMembersByClubId(long clubId, int pageNo, int pageSize) {
        if (clubId < 1) {
            return null;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select id from ac_member_info where 1=1 ");
        sql.appendSql(" and club_id = ").appendValue(clubId);
        Pager<ClubMember> pager = pagerService.pager(ClubMember.class, sql, pageNo, pageSize);
        if (pager.getResultList() != null && !pager.getResultList().isEmpty()) {
            pager.setResultList(userService.loadTransientUsers(pager.getResultList()));
        }
        return pager;
    }

    /**
     * 取消申请加入车友会
     *
     * @param memberApplicationId
     */
    public void deleteMemberApplication(MemberApplication memberApplication) {
        memberApplication.setStatus(-2);
        geliDao.update(memberApplication);
    }

    /**
     * 取消申请加入车友会，有返回结果
     *
     * @param memberApplication
     * @param 返回操作结果
     */
    public int deleteMemberApplicationReturn(MemberApplication memberApplication) {
        memberApplication.setStatus(-2);
        return geliDao.update(memberApplication);
    }

    /**
     * 编辑后台删除会员
     *
     * @param adminId
     * @param memberId
     */
    public int removeMemberByEdit(int type, long adminId, long memberId) {
        int result = 0;
        if (memberId < 1 || type < 1) {
            return result;
        }
        if (type == 1) {
            //删除申请中的信息
            MemberApplication member = geliDao.find(MemberApplication.class, memberId);
            geliDao.delete(member, memberId);
            result = 1;
        } else if (type == 2) {
            //删除会员信息
            ClubMember member = geliDao.find(ClubMember.class, memberId);
            if (member != null && member.getAdminType() != ClubMember.ADMIN) {
                geliDao.delete(member, memberId);
                result = 1;
            }
        }
        return result;
    }

    /**
     * 删除多余的申请信息 申请 -- 申请
     */
    public void delMoreMacation() {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT id FROM ac_member_application where status = 0  GROUP BY user_id HAVING COUNT(user_id)>1");
        List<MemberApplication> list = geliDao.list(MemberApplication.class, sql.getSql(), sql.getValues());
        if (list != null && !list.isEmpty()) {
            for (MemberApplication application : list) {
                List<MemberApplication> listApply = this.findMemberApplication(application.getUid(), 0, false);
                if (listApply != null && !listApply.isEmpty()) {
                    boolean isDel = false;
                    for (int i = 0; i < listApply.size(); i++) {
                        MemberApplication apply = listApply.get(i);
                        if (i > 0 && apply.getStatus() >= 0 && isDel) {
                            geliDao.delete(apply, apply.getId());
                        }
                        if (apply.getStatus() >= 0) {
                            isDel = true;
                        }
                    }
                }
            }
        }

    }

    /**
     * 编译用删除多余的用户信息
     */
    public void delMemberByEdit() {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT id FROM ac_member_info  GROUP BY user_id HAVING COUNT(user_id)>1 ");
        List<ClubMember> listMember = geliDao.list(ClubMember.class, sql.getSql(), sql.getValues());
        if (listMember != null && !listMember.isEmpty()) {
            for (ClubMember member : listMember) {
                List<ClubMember> myClub = this.listClubMemberByUser(member.getUid());
                if (myClub != null && !myClub.isEmpty()) {
                    long id = 0L;
                    for (int i = 0; i < myClub.size(); i++) {
                        ClubMember mem = myClub.get(i);
                        if (mem.getAdminType() != ClubMember.ADMIN) {
                            id = mem.getId();
                        }
                    }
                    if (id > 0) {
                        ClubMember delMem = geliDao.find(ClubMember.class, id);
                        geliDao.delete(delMem, delMem.getId());
                        this.delMembByUidOrCid(delMem.getClubId(), delMem.getUid());

                        Club club = clubService.getClub(delMem.getClubId());
                        club.setMemberNum(club.getMemberNum() - 1);
                        User user = userService.findUser(delMem.getUid());
                        if (user != null && user.getVip() > 0) {
                            club.setVipNum(club.getVipNum() - 1);
                        }
                        geliDao.update(club, "member_num,vip_num");
                    }
                }
            }
        }
        updateUserJoinNum();
    }

    /**
     * 更新用户加入车友会实际数据
     */
    public void updateUserJoinNum() {
        String upSql = " UPDATE ac_user SET joined_club_count = (SELECT COUNT(user_id) FROM ac_member_info WHERE user_id  = ac_user.user_id) ";
        jdbcTemplate.update(upSql);
    }

    /**
     * 根据用户ID和车友会ID删除申请表信息
     *
     * @param clubId
     * @param userId
     */
    public void delMembByUidOrCid(long clubId, long userId) {
        String sql = " delete from ac_member_application where user_id = ? and club_id = ?";
        this.jdbcTemplate.update(sql, clubId, userId);
    }

    /**
     * 组装系统消息
     *
     * @param adminType 职务 1 副会长，2会长，0 解除
     * @param oldAdminType 原有职务
     * @param clubMember 车友会会员信息
     * @param clubIndexUrl 车友会URL
     * @param club 车友会
     * @return
     */
    private String parsePassMsg(int adminType, int oldAdminType, ClubMember clubMember, Club club) {
//        String clubUrl = clubService.getClubIndexUrl(club.getClubId(), club.getClubUrl());
        String clubIndexUrl = messageService.assembleClubUrl(club.getClubUrl(), club.getName());
        if (adminType == ClubMember.ADMIN) {
            //转移会长发消息通知
            return String.format(MessageService.UP_ADMIN1, clubIndexUrl, club.getName());
        } else if (adminType == ClubMember.VICE_ADMIN) {
            return String.format(MessageService.UP_ADMIN2, clubIndexUrl, club.getName());
        } else if (adminType == ClubMember.NON_ADMIN) {
            //只有在接触副会长才发消息
            if (oldAdminType == ClubMember.VICE_ADMIN) {
                return String.format(MessageService.DOWN_ADMIN, clubIndexUrl, "副会长");
            }
            return null;
        }
        return null;
    }

    /**
     * 根据会员列表返回用户列表
     *
     * @param members
     * @return
     */
    public List<User> getUserByMember(List<ClubMember> members) {
        if (members == null) {
            return null;
        }
        List<User> users = new ArrayList<User>();
        for (ClubMember m : members) {
            User u = userService.findUser(m.getUid());
            if (u != null) {
                users.add(u);
            }
        }
        return users;
    }

    public List<MemberStat> initCacheMemberStat() {

        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select * from ac_member_stat ");
        return geliDao.list(MemberStat.class, sql.getSql());
    }

    public List<ClubTotalMember> initToalMember() {

        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select club_id from ac_club_total_member ");
        return geliDao.list(ClubTotalMember.class, sql.getSql());
    }

    public MemberApplication getMemberApplication(long clubId, long userId, int status) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select * from ac_member_application where club_id=").appendValue(clubId);
        sql.appendSql(" and user_id=").appendValue(userId);
        if (status != 99) {
            sql.appendSql(" and status=").appendValue(status);
        }
        sql.appendSql(" order by create_at desc");
        return geliDao.findFirst(MemberApplication.class, sql.getSql(), sql.getValues());
    }

    /**
     * 获取用户以前的精华帖放到车友会里面
     *
     * @param clubId
     * @param userId
     */
    public List<Topic> loadPickByUserIdClubId(long clubId, long userId) {
        List<Topic> topicList = new ArrayList<Topic>();
        if (clubId < 1 || userId < 1) {
            return topicList;
        }
        JSONObject objArr = intfService.getPickByUserId(userId);

        if (objArr != null) {
            JSONArray array = objArr.getJSONArray("list");
            if (array != null && !array.isEmpty()) {
                for (int i = 0; i < array.size(); i++) {
                    JSONObject obj = array.getJSONObject(i);

                    Topic topic = new Topic();
                    topic.setClubId(clubId);
                    topic.setUserId(userId);
                    topic.setForumId(obj.getLong("forumId"));
                    topic.setForumName(obj.getString("forumName"));
                    topic.setTopicId(obj.getLong("tid"));
                    topic.setTopicTitle(obj.getString("title"));
                    topic.setMessage(obj.getString("content"));
                    topic.setCreateAt(WebUtils.parseDate("20" + obj.getString("createAt")));
                    topic.setCreateDate(WebUtils.parseDate("20" + obj.getString("createAt")));
                    topic.setPetrol(0);
                    topic.setLastPostBy(obj.getLong("lastPostBy"));
                    String lastPostNickName = userService.getNickNameByUserId(obj.getLong("lastPostBy"));
                    topic.setLastPostName(lastPostNickName);
                    topic.setPick(1);
                    topic.setRecommend(0);
                    topic.setImage1(obj.getString("image1"));
                    topic.setImage2(obj.getString("image2"));
                    topic.setImage3(obj.getString("image3"));
                    topic.setLastPostAt(WebUtils.parseDate("20" + obj.getString("lastPostAt")));
                    topic.setPostCount(obj.getInteger("replyCount"));
                    geliDao.create(topic);
                    topicList.add(topic);
                    //同步精华帖动态
                    sysncPickDyna(obj, clubId, userId, lastPostNickName);
                }
            }
        }
        return topicList;
    }

    private void sysncDynaReply(long topicId) {
        String url = systemConfig.getBbsRoot() + "/intf/common/_topicRepliesJson.jsp?filterQuote=1&applicationType=autoclub&desc=true&pageSize=11&tid=" + topicId;//4084163
        String result = simpleHttpTemplate.get(url, "http://club.pcauto.com.cn");
        log.debug("\nget replies\nurl: {}\nresult:\n{}", url, result);
        if (StringUtils.isNotEmpty(result)) {
            JSONObject obj = null;
            JSONArray arr = null;
            try {
                obj = JSONObject.parseObject(result);
                if (obj != null) {
                    arr = obj.getJSONArray("postList");
                }
            } catch (JSONException e) {
                log.error("fastJson解析result出错！\nget replies\nurl: {}\nresult:\n{}", url, result);
            }
            if (obj == null || arr == null) {
                return;
            }
            for (int i = 0; i < arr.size(); i++) {
                JSONObject o = arr.getJSONObject(i);
                if (o == null) {
                    continue;
                }
                PostReply reply = new PostReply();
                String dateStr = o.getString("createAt");
                Date date = DateUtils.parseDate(dateStr, "yy-MM-dd HH:mm");
                reply.setCreateAt(date);
                reply.setTopicCreateAt(date);
                JSONObject author = o.getJSONObject("author");
                if (author == null) {
                    continue;
                }
                reply.setCreateName(author.getString("nickname"));
                reply.setUserId(author.getLongValue("authorId"));
                reply.setFloor(o.getIntValue("floor"));
                reply.setMessage(o.getString("message"));
                reply.setPostId(o.getLongValue("postId"));
                reply.setTopicId(o.getLongValue("topicId"));
                //过滤掉引用楼层的信息
                reply.setIsQuote(false);
                reply.setPostToPostId(0);
                dynaService.replyTopicDyna(reply);
            }
        }
    }

    private void sysncPickDyna(JSONObject obj, long clubId, long userId,
            String lastPostNickName) {
        User user = userService.findUser(userId);
        if (user == null) {
            return;
        }

        TopicDyna topicDyna = new TopicDyna();
        topicDyna.setClubId(clubId);
        topicDyna.setCreateDate(WebUtils.parseDate("20" + obj.getString("createAt")));
        topicDyna.setCreateName(user.getNickname() == null ? user.getUsername() : user.getNickname());
        topicDyna.setForumId(obj.getLong("forumId"));
        topicDyna.setForumName(obj.getString("forumName"));
        topicDyna.setLastPostAt(WebUtils.parseDate("20" + obj.getString("lastPostAt")));
        if (obj.getLongValue("lastPostBy") == 0 || StringUtils.isBlank(lastPostNickName)) {
            topicDyna.setLastPostBy(userId);
            topicDyna.setLastPostName(user.getNickname() == null ? user.getUsername() : user.getNickname());
        } else {
            topicDyna.setLastPostBy(obj.getLongValue("lastPostBy"));
            topicDyna.setLastPostName(lastPostNickName);
        }
        topicDyna.setMessage(obj.getString("content"));
        topicDyna.setPetrol(0);
        topicDyna.setPick(1);
        topicDyna.setPostCount(obj.getInteger("replyCount"));
        topicDyna.setRecommend(0);
        topicDyna.setTopicId(obj.getLong("tid"));
        topicDyna.setTopicTitle(obj.getString("title"));
        topicDyna.setUserId(userId);
        topicDyna.setType(obj.getString("type"));//帖子类型，question为提问帖标记

        //保存图片
        List<DynaImage> imageList = new ArrayList<DynaImage>();
        JSONArray imageArray = obj.getJSONArray("images");
        if (!imageArray.isEmpty()) {
            for (int i = 0; i < imageArray.size(); i++) {
                JSONObject imageObject = imageArray.getJSONObject(i);
                DynaImage dynaImage = new DynaImage();
                dynaImage.setSeq(imageObject.getIntValue("seq"));
                dynaImage.setUrl(imageObject.getString("url"));
                dynaImage.setWidth(imageObject.getIntValue("width"));
                dynaImage.setHeight(imageObject.getIntValue("height"));
                if (StringUtils.isBlank(dynaImage.getUrl())) {
                    continue;//不符合规则的图片不存到数据库
                }
                imageList.add(dynaImage);
            }
        }
        dynaService.createTopicDyna(topicDyna, imageList);
        //同步回复要在帖子创建之后
        sysncDynaReply(topicDyna.getTopicId());
    }

    /**
     * 获取所有会员信息
     *
     * @return
     */
    public List<ClubMember> initMemberInfo() {

        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select id from ac_member_info");
        return geliDao.list(ClubMember.class, sql.getSql());
    }

    /**
     * 根据用户ID，得到参数的车友会个数
     *
     * @param userId
     * @return
     */
    public int countMemberByUid(long userId) {
        String sql = "select count(1) from ac_member_info where user_id = ?";
        return geliDao.count(sql, userId);
    }

    /**
     * 根据用户ID，判断是否为车友会成员
     *
     * @param userId
     * @return
     */
    public boolean isClubMember(long userId) {
        int count = this.countMemberByUid(userId);
        if (count == 0) {
            return false;
        }
        return true;
    }

    /**
     * 根据clubId得到已经获取到融云Token的用户列表
     *
     * @param userIdList
     * @return
     */
    public List<User> getUserByClubIdAndToken(long clubId) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select b.user_id from ac_member_info a, ac_user b where a.user_id = b.user_id and b.token is not null ");
        sql.appendSql(" and a.club_id = ").appendValue(clubId);
        return geliDao.list(User.class, sql.getSql(), sql.getValues());
    }

    /**
     * 通过用户ID和车友会ID批量查询会员
     *
     * @param clubId
     * @param userIds
     * @return
     */
    public List<ClubMember> getClubMemberByIdList(long clubId, Object[] userIds) {
        if (userIds != null && userIds.length > 0) {
            SqlBuilder sql = new SqlBuilder();
            sql.appendSql("select id from ac_member_info where user_id in").appendValues(userIds)
                    .appendSql(" and club_id=").appendValue(clubId);
            List<ClubMember> clubMembers = geliDao.list(ClubMember.class, sql.getSql(), sql.getValues());
            if (clubMembers != null && !clubMembers.isEmpty()) {
                userService.loadTransientUsers(clubMembers);
            }
            return clubMembers;
        } else {
            return Collections.emptyList();
        }
    }

    /**
     * 根据车友会ID获取会长信息
     *
     * @param clubId
     * @return
     */
    public ClubMember findAdminByClubId(long clubId) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select * from ac_member_info where club_id = ").appendValue(clubId);
        sql.appendSql(" and admin_type = ").appendValue(ClubMember.ADMIN);
        return geliDao.findFirst(ClubMember.class, sql.getSql(), sql.getValues());
    }

    /**
     * 根据车友会ID获取认证会员或普通会员列表(非认证会员为普通) TODO
     * 关于查询用户列表有好几个方法类似，有些写得还有点奇怪，可以参照OMemberService优化下
     *
     * @param clubId
     * @param isVip 0非 1是
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Pager<ClubMember> listVipMember(long clubId, int isVip, int pageNo, int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select c.id from ").appendSql(geliOrm.getTableName(ClubMember.class)).appendSql(" c,").appendSql(geliOrm.getTableName(User.class)).appendSql(" b where b.vip=").appendValue(isVip);
        sql.appendSql(" and c.user_id=b.user_id and c.club_id=").appendValue(clubId).appendSql(" order by c.admin_type desc, join_at ASC");
        Pager<ClubMember> members = pagerService.pager(ClubMember.class, sql, pageNo, pageSize);
        if (members != null) {
            userService.loadTransientUsers(members.getResultList());
        }
        return members;
    }
    /**
     * 查询某个车友会所有认证会员列表
     * @param clubId
     * @return 
     */
    public List<ClubMember> listVipMember(long clubId) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select c.id from ").appendSql(geliOrm.getTableName(ClubMember.class)).appendSql(" c,").appendSql(geliOrm.getTableName(User.class)).appendSql(" b where b.vip=").appendValue(ClubMember.IS_VIP);
        sql.appendSql(" and c.user_id=b.user_id and c.club_id=").appendValue(clubId).appendSql(" order by c.admin_type desc, join_at ASC");
        List<ClubMember> members = geliDao.list(ClubMember.class, sql.getSql(),sql.getValues());
        return members;
    }
    public int updateClubMember(ClubMember clubMember) {
        clubMember.setUpdateAt(new Date());
        return geliDao.update(clubMember);
    }

    /**
     * 关键字查询会员
     *
     * @param clubId
     * @param keyword
     * @param pageNo
     * @param pageSize
     * @param isLoadSeries 是否匹配车系
     * @return
     */
    public Pager<ClubMember> searchMember(long clubId, String keyword, int pageNo, int pageSize, boolean isLoadSeries) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select c.id from ").appendSql(geliOrm.getTableName(ClubMember.class)).appendSql(" c,").appendSql(geliOrm.getTableName(User.class)).appendSql(" u where ");
        if (isLoadSeries) {
            sql.appendSql("(");
        }
        sql.appendSql(" u.nickname like").appendValue("%" + keyword + "%");
        if (isLoadSeries) {
            sql.appendSql(" or u.car_info like ").appendValue("%" + keyword + "%")
                    .appendSql(" or (u.vip=1 and u.vip_info like ").appendValue("%" + keyword + "%")
                    .appendSql("))");
        }
        sql.appendSql(" and c.user_id=u.user_id and c.club_id=").appendValue(clubId);
        Pager<ClubMember> members = pagerService.pager(ClubMember.class, sql, pageNo, pageSize);
        if (members != null) {
            userService.loadTransientUsers(members.getResultList());
        }
        return members;
    }

    /**
     * 查询某车友会下的所有成员数
     *
     * @param clubId
     * @return
     */
    public int countMembersByClubId(long clubId) {
        String sql = "select count(1) from ac_member_info where club_id = ?";
        return geliDao.count(sql, clubId);
    }

    /**
     * 根据用户ID，查找被审核当中的相关用户信息列表
     *
     * @param userId
     * @param status
     * @return
     */
    public List<MemberApplication> listMemberApplication(long userId, int status) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select * from ac_member_application where 1=1");
        sql.appendSql(" and user_id=").appendValue(userId);
        if (status != 99) {
            sql.appendSql(" and status=").appendValue(status);
        }
        sql.appendSql(" order by create_at desc");
        return geliDao.list(MemberApplication.class, sql.getSql(), sql.getValues());
    }

    /**
     * 根据车友会获取最新加入的几位成员
     *
     * @param clubId
     * @param size
     * @return
     */
    public List<ClubMember> getLastestMember(long clubId, int size) {
        if (clubId <= 0) {
            return Collections.emptyList();
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select id from ").appendSql(geliOrm.getTableName(ClubMember.class)).appendSql(" where 1=1 ");
        sql.appendSql(" and club_id = ").appendValue(clubId);
        sql.appendSql(" order by join_at desc ");
        try {
            return geliDao.page(ClubMember.class, sql.getSql(), 1, size, sql.getValues());
        } catch (DataAccessException e) {
            log.warn("empty result!sql:{} values:{},pageNo:{},pageSize:{}",
                    sql.getSql(), StringUtil.toString(sql.getValues()), 1, size);
            return Collections.emptyList();
        }
    }

    /**
     * 加入车友会(创建会员信息)
     *
     * @return
     */
    public long createMember(User user, long clubId, int adminType) {
        //创建会员信息
        long id = createMembership(user, clubId, adminType);
        //拿取该人所有精华帖
        this.loadPickByUserIdClubId(clubId, user.getUid());
        return id;
    }

    /**
     * 创建会员相关(发动态)
     *
     * @param clubId
     * @param uid
     * @return
     */
    public long createMemberInfo(long clubId, User user, int adminType) {
        long memberId = createMember(user, clubId, adminType);
        if (memberId > 0) {
            AbstractDyna dyna = new AbstractDyna();
            dyna.setClubId(clubId);
            dyna.setContent("");//内容固定，页面拼凑就好了
            dyna.setCreateBy(user.getUid());
            dyna.setCreateDate(new Date());
            dyna.setCreateName(userService.findUser(user.getUid()).getNickname());
            dyna.setTargetId(0);
            dynaService.createJoinClubDyna(dyna, null);
        }
        return memberId;
    }
}
